﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using Business.Base.DB.DBRules;
using Business.CentrifugalPump.Perf;
using Business.NPSH;
using Business.ScrewPump.Perf;
using Configuration;

using System.Linq;
using Business.Base.DB.Query;
using Log;
using Business.Base.Utils;
using System.Diagnostics;
using MySqlConnector;

namespace Business.Base.DB
{
    class MysqlDBManager : IDBManager
    {
        private String fields;
        private String aValues;
        private String table;
        private Type type;
        private Dictionary<string, string> fieldMap;
        private int objectId;
    

        private MySqlConnection  connection = null;
        private MySqlCommand     sqlSelectCountCommand = null;
        private MySqlCommand     sqlSelectCommand = null;
        
        private MySqlDataAdapter sqlSelectAdapter = null;
        private MySqlCommand     sqlInsertCommand = null;
        private MySqlCommand     sqlDeleteCommand = null;
        private MySqlCommand     sqlUpdateCommand = null;

        public static Dictionary<string, MysqlDBManager> dbManagerDic;

        public String ObjectId { get => this.table+ objectId.ToString(); }

        public static void init()
        {
            dbManagerDic = new Dictionary<string, MysqlDBManager>();
            Type[] types = new Type[]
                {  typeof(CentrifugalPumpPerfExperiment),
                   typeof(CentrifugalPumpPerfExperimentRecord),
                   typeof(ScrewPumpPerfExperiment),
                   typeof(ScrewPumpPerfExperimentRecord),
                   typeof(NpshExperiment),
                   typeof(NpshExperimentRecord)

                };
            int id = 0;
            foreach (Type type in types)
            {
                dbManagerDic.Add(type.Name, new MysqlDBManager(id++,type));
            }

        }

        static MysqlDBManager()
        {
        }

        MysqlDBManager(int id , Type type)
        {
            this.objectId = id;
            DBMapping dbMapping = DBMappingRules.mappingDic[type.Name];
            Dictionary<string, string> dic = dbMapping.Mapping;
            IEnumerator enumerator = dic.Keys.GetEnumerator();
            StringBuilder sbFileds = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            List<string> keys = new List<string>();
            int counter = 0;
            while (enumerator.MoveNext())
            {
                String key = (String)enumerator.Current;
                keys.Add(key);
                if (counter == 0)
                {
                    sbFileds.Append(key);
                    sbValues.Append("@" + dic[key]);
                }
                else
                {
                    sbFileds.Append("," + key);
                    sbValues.Append(",@" + dic[key]);
                }
                counter++;
            }

            this.type = type;
            this.table = dbMapping.TableName;
            this.fields = sbFileds.ToString();
            this.aValues = sbValues.ToString();
            this.fieldMap = dic;

            String connectionString = DataSourceConfig.DataSourceString;
            connection = new MySqlConnection(connectionString);


            sqlSelectCountCommand = new MySqlCommand();
            sqlSelectCountCommand.Connection = connection;

            sqlSelectCommand = new MySqlCommand();
            sqlSelectCommand.Connection = connection;
            


            sqlInsertCommand = new MySqlCommand();
            sqlInsertCommand.Connection = connection;
            sqlInsertCommand.CommandText = "INSERT INTO " + table + " (" + fields + ") VALUES (" + aValues + ")";

            sqlDeleteCommand = new MySqlCommand();
            sqlDeleteCommand.Connection = connection;
            sqlDeleteCommand.CommandText = "delete from " + table;

            sqlUpdateCommand = new MySqlCommand();
            sqlUpdateCommand.Connection = connection;


            sqlSelectAdapter = new MySqlDataAdapter();
            sqlSelectAdapter.SelectCommand = sqlSelectCommand;
           

            
        }



        /*
        public int GetCount(BaseQuery query)
        {
            sqlSelectCountCommand.CommandText = "select count(*) from " + table + query.ToSQLConditionWithOutSort();
            connection.Open();
            object result = sqlSelectCountCommand.ExecuteScalar();
            connection.Close();
            return (int)result;

        }
              

        private String getPageQuery(BaseQuery query)
        {
            String pageQuery = " offset " + (query.PageNum - 1) * query.PageSize + " rows fetch next " + query.PageSize + " rows only;";
            return pageQuery;
        }
     */

        public int GetCount(BaseQuery query)
        {
            sqlSelectCountCommand.CommandText = "select count(*) from " + table + query.ToSQLConditionWithOutSort();
            object result = null;
            try
            {
                connection.Open();
                result = sqlSelectCountCommand.ExecuteScalar();
                connection.Close();
            }
            finally
            {
                try
                {
                    if(connection!=null&&connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                    
                }
                catch(Exception ex)
                {
                    throw ex;
                }

            }
            return (int)result;

        }
    
        private String getPageQuery(BaseQuery query)
        {
            if (query.PageNum == -1)
            {
                return "";
            }

            String pageQuery = " limit " + (query.PageNum - 1) * query.PageSize + "," + query.PageSize + " ;";
            return pageQuery;
        }


        DataTable GetDataList(BaseQuery query)

        { 
            sqlSelectCommand.CommandText = "select Id," + this.fields + " from " + this.table + query.ToSQLConditionString() + this.getPageQuery(query);

            DataSet dataSet = new DataSet();
            sqlSelectAdapter.Fill(dataSet);
            //bool b1 =object.ReferenceEquals(connection,sqlDeleteCommand.Connection);
            //bool b2 =object.ReferenceEquals(connection, sqlSelectAdapter.SelectCommand.Connection);
            return dataSet.Tables[0];
        }

        private void assignValue(DataRow row, ref Object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties();
            Dictionary<string, string> dic = new Dictionary<string, string>();

            foreach (PropertyInfo propertyInfo in properties)
            {
                if (!propertyInfo.CanWrite)
                {
                    continue;
                }
                DBFieldAttribute dbFieldAttribute = propertyInfo.GetCustomAttribute<DBFieldAttribute>();
                if (dbFieldAttribute == null)
                {
                    continue;
                }
                string name = dbFieldAttribute.FiledName;
                try
                {
                    propertyInfo.SetValue(obj, row[name]);
                }
                catch (System.ArgumentException ex)
                {
                    if (propertyInfo.PropertyType ==typeof(Boolean) && row[name].GetType()==typeof(int))
                    {
                       Console.WriteLine( ex.ToString());
                    }
                }
                
            }
        }

        public List<Object> Query(BaseQuery query)
        {
            DataTable table = this.GetDataList(query);
            List<Object> list = new List<object>();
            string name = type.FullName;
            Assembly assembly = Assembly.GetExecutingAssembly(); // 获取当前程序集
            foreach (DataRow row in table.Rows)
            {
                
                Object obj = assembly.CreateInstance(name);
                assignValue(row, ref obj);
                list.Add(obj);
            }
            return list;
        }

        public static object handleInsertFieldValue(Type type, String field, object obj)
        {
            if (obj.GetType().Equals(typeof(Double)))
            {
                Double d = (Double)obj;
                if (Double.IsNaN(d))
                {
                    return null;
                }
            }
            return obj;
        }

        private int InertMap(Dictionary<String, Object> fieldMap)
        {
            sqlInsertCommand.Parameters.Clear();
            
            foreach (KeyValuePair<String, object> kv in fieldMap)
            {                
                sqlInsertCommand.Parameters.Add(new MySqlParameter("@" + kv.Key, kv.Value));
            }
            long id = -1;
            
            try
            {
                connection.Open();
                //Debug.WriteLine("sql:" + sqlInsertCommand.CommandText);
                sqlInsertCommand.ExecuteScalar();
                //Debug.WriteLine("id:" + id);
                id = sqlInsertCommand.LastInsertedId;
                connection.Close();
            }
            catch(Exception ex)
            {
                String formateInfo = "";
                formateInfo += "cmd:" + sqlInsertCommand.CommandText + "\r";
                formateInfo += "message:" + ex.Message + "\r";
                formateInfo += " details: " + ex.ToString();
                LogHelper.LoggerMsg(LogLevel.Error, formateInfo);
                throw ex;
            }
            finally
            {
                try
                {
                    if (connection != null && connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }

                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
           

            return (int)Convert.ToInt32(Convert.ToDouble(id));

        }


        public int Insert(object obj)
        {
            try
            {
                sqlInsertCommand.Parameters.Clear();

                string[] fieldNames
                    = this.fields.Split(new char[] { ',' });
                Dictionary<String, Object> fieldValueMap = new Dictionary<string, object>();


                IEnumerator enumerator = fieldMap.Values.GetEnumerator();

                while (enumerator.MoveNext())
                {
                    String propertyName = (String)enumerator.Current;
                    try
                    {
                        Type Ts = obj.GetType();
                        object o = Ts.GetProperty(propertyName).GetValue(obj, null);
                        o = handleInsertFieldValue(this.type, propertyName, o);
                        //if (null == o)
                        // continue;
                        fieldValueMap.Add(propertyName, o);


                    }
                    catch (Exception e)
                    {


                        throw e;
                    }
                }


                return InertMap(fieldValueMap);
            }
            catch (Exception ex)
            {
                String formateInfo = BOFormater.FormateObj("insert", obj);
                formateInfo += "cmd:"+ sqlInsertCommand.CommandText +"\r";
                formateInfo += "message:"+ex.Message +"\r";
                formateInfo += " details: " + ex.ToString();
                LogHelper.LoggerMsg(LogLevel.Error,formateInfo);
                throw ex;
            }
        }


        

        public void Delete(Object id)
        {
            if (id != null)
            {
                this.sqlDeleteCommand.CommandText = "delete from " + table + " where id = @id";
                sqlDeleteCommand.Parameters.Add(new MySqlParameter("@id", id));
            }
            try
            { 
            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            sqlDeleteCommand.Parameters.Clear();
            connection.Close();
            }
            finally
            {
                try
                {
                    if (connection != null && connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }

                }
                catch (Exception ex)
                {
                    String formateInfo = "";
                    formateInfo += "cmd:" + sqlDeleteCommand.CommandText + "\r";
                    formateInfo += "message:" + ex.Message + "\r";
                    formateInfo += " details: " + ex.ToString();
                    LogHelper.LoggerMsg(LogLevel.Error, formateInfo);
                    throw ex;
                }

            }
        }


        public void DeleteByCondtion(string condition)
        {
            this.sqlDeleteCommand.CommandText = "delete from " + table + " where " + condition;
            try
            {
                connection.Open();
                sqlDeleteCommand.ExecuteNonQuery();
                sqlDeleteCommand.Parameters.Clear();
                connection.Close();
            }
            finally
            {
                try
                {
                    if (connection != null && connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }

                }
                catch (Exception ex)
                {
                    String formateInfo = "condition："+condition+"\r";
                    formateInfo += "cmd:" + sqlDeleteCommand.CommandText + "\r";
                    formateInfo += "message:" + ex.Message + "\r";
                    formateInfo += " details: " + ex.ToString();
                    LogHelper.LoggerMsg(LogLevel.Error, formateInfo);
                    throw ex;
                }

            }
        }

        public void Update1(int id, object obj)
        {
            string[] fieldNames
                = this.fields.Split(new char[] { ',' });
            Dictionary<String, Object> fieldValueMap = new Dictionary<string, object>();


            IEnumerator enumerator = fieldMap.Values.GetEnumerator();

            while (enumerator.MoveNext())
            {
                String propertyName = (String)enumerator.Current;
                try
                {
                    Type Ts = obj.GetType();
                    object o = Ts.GetProperty(propertyName).GetValue(obj, null);
                    o = handleInsertFieldValue(this.type, propertyName, o);
                    //if (null == o)
                    // continue;
                    fieldValueMap.Add(propertyName, o);


                }
                catch (Exception e)
                {
                    throw e;
                }
            }

        }

        public void Update(int id, object obj)
        {
            StringBuilder sb = new StringBuilder();
            sqlUpdateCommand.Parameters.Clear();

            sb.Append("update " + table + " set ");


            List<string> keys = this.fieldMap.Keys.ToList();
            int counter = 0;

            for (int i = 0; i < keys.Count; i++)
            {
                String propertyName = this.fieldMap[keys[i]];
                try
                {
                    Type Ts = obj.GetType();
                    //Console.WriteLine(propertyName + "----");
                    object o = Ts.GetProperty(propertyName).GetValue(obj, null);
                    //Console.WriteLine(propertyName + " " + o.ToString());
                    if (null == o)
                    {
                        counter++;
                        continue;
                    }

                    if (i == (keys.Count - 1))
                    {
                        sb.Append(keys[i] + "=" + "@" + keys[i]);
                    }
                    else
                    {
                        sb.Append(keys[i] + "=" + "@" + keys[i] + ",");
                    }
                    sqlUpdateCommand.Parameters.AddWithValue("@" + keys[i], o);
                }
                catch (Exception e)
                {
                    throw e;
                }


            }
            sb.Append(" where id = " + id);
            sqlUpdateCommand.CommandText = sb.ToString();
            //更新的对象所有属性都是空的！
            if (counter == keys.Count)
            {
                throw new Exception();
            }
            try { 

                connection.Open();
                sqlUpdateCommand.ExecuteNonQuery();
                connection.Close();

            }
            finally
            {
                try
                {
                    if (connection != null && connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }

}
                catch (Exception ex)
                {
                    String formateInfo = BOFormater.FormateObj("update", obj);
                    formateInfo += "cmd:" + this.sqlUpdateCommand.CommandText + "\r";
                    formateInfo += "message:" + ex.Message + "\r";
                    formateInfo += " details: " + ex.ToString();
                    LogHelper.LoggerMsg(LogLevel.Error, formateInfo);
                    throw ex;
                    throw ex;
                }

            }
        }
    }
}
